package edu.zju.gis.dbfg.server.util;

import edu.zju.gis.dbfg.server.model.ExternalDataArgs;
import edu.zju.gis.dbfg.server.model.vo.FeatureRecord;
import edu.zju.gis.dbfg.server.model.vo.tableRecordsWithTotalsize;
import lombok.Getter;
import lombok.Setter;
import org.json.JSONArray;
import org.json.simple.JSONObject;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


@Getter
@Setter
public abstract class ExternalHelper {
    protected Connection connection;
    protected JSONObject res;

    public abstract JSONObject testConnection(ExternalDataArgs sourceConnect) throws Exception;

    public abstract List<JSONObject> queryConstruct(ExternalDataArgs sourceConnect) throws Exception;

    public abstract tableRecordsWithTotalsize pageSearh(ExternalDataArgs sourceConnect, int pageNo, int pageSize) throws Exception;

    public boolean close() {
        try {
            if (this.connection != null || !this.connection.isClosed())
                this.connection.close();
            if (this.res != null)
                this.res = null;
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    public List<JSONObject> querySql(Statement stmt, String sql, String columnName) throws Exception {
        List<JSONObject> resObject = new ArrayList<>();
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("value", rs.getString(columnName));
            jsonObject.put("label", rs.getString(columnName));
            resObject.add(jsonObject);
        }
        rs.close();
        stmt.close();
        return resObject;
    }

    public tableRecordsWithTotalsize search(Statement stmt, String sql, String sqlAll, String spatialField) throws Exception {
//     String sql1 = "select * from " + tableName;
        ResultSet rs = stmt.executeQuery(sql);
        tableRecordsWithTotalsize res = new tableRecordsWithTotalsize();
        //获取列名
        ResultSetMetaData rsmd = rs.getMetaData();
        int count = rsmd.getColumnCount();
        String[] name = new String[count];
        int[] type = new int[count];
        for (int i = 0; i < count; i++) {
            name[i] = rsmd.getColumnName(i + 1);
            type[i] = rsmd.getColumnType(i + 1);
        }
        List<JSONObject> records = new ArrayList<>();

        if (spatialField.equals("none")) {
            while (rs.next()) {
                JSONObject jsonObject = new JSONObject();
                for (int i = 0; i < count; i++) {
                    String key = name[i];
                    //不管原来的key是什么类型都可以用getString
                    String value = rs.getString(key);
                    jsonObject.put(key, value);
                }
                records.add(jsonObject);
            }
            res.setRecords(records);
        } else {
            while (rs.next()) {
                JSONObject jsonObject = new JSONObject();
                for (int i = 0; i < count; i++) {
                    String key = name[i];
                    if (!key.equals(spatialField)) {
                        //不管原来的key是什么类型都可以用getString
                        String value = rs.getString(key);
                        jsonObject.put(key, value);
                    }
                }
                records.add(jsonObject);
            }
            res.setRecords(records);
        }
        ResultSet rsAll = stmt.executeQuery(sqlAll);
        int rowCount = 0;
        while (rsAll.next()) {
            rowCount++;
        }
        res.setTotalSize(rowCount);
        rs.close();
        stmt.close();
        return res;
    }
    public abstract FeatureRecord identifyQuery(String lon, String lat, ExternalDataArgs sourceConnect) throws Exception;

    public abstract List<FeatureRecord> areaQuery(JSONArray areaArray, ExternalDataArgs sourceConnect) throws Exception;

}
